<?php
/* 文字图像
*
*
*/

namespace hpnWse {


/// SQL实用
class stSqlUtil
{
	/// 查询计数，每次调用PDO:execute()会递增1
	public static $c_QryCnt = 0;

	/// 标识符 - 表（`数据库`.`表`）
	/// a_Dtbs: String，数据库，默认null
	/// a_Tbl: String，表格，必须有效
	public static function cId_Tbl($a_Dtbs, $a_Tbl)
	{
		$l_FullTbl = '`' . $a_Tbl . '`';
		if (fBool($a_Dtbs))
		{ $l_FullTbl = '`' . $a_Dtbs . '`.' . $l_FullTbl; }
		return $l_FullTbl;
	}

	/// 标识符 - 列（`列`）
	/// a_Col：String，列
	public static function cId_Col($a_Col)
	{
		return '`' . $a_Col . '`';
	}

	/// 移除危险字符，包括“'"/\”
	public static function cRmvDngrCha($a_Str)
	{
		return preg_replace('/[\'"\\/\\\\]/', '', $a_Str);
	}

	/// 转义值，即"a'b"->"a''b"
	/// a_Wrap：Boolean，包装进引号里？若true则"a'b"->"'a''b'"
	public static function cEscpVal($a_Val, $a_Wrap = false)
	{
		$a_Val = str_replace("'", "''", $a_Val);
		$a_Val = str_replace('"', '""', $a_Val);
		if ($a_Wrap)
		{ $a_Val = "'" . $a_Val . "'"; }
		return $a_Val;
	}

	/// 列数组<-列字符串
	public static function cColAryFromStr($a_Cols)
	{
		if (is_string($a_Cols))
		{ $a_Cols = explode(',', $a_Cols); }

		for ($i=0; $i<count($a_Cols); ++$i)
		{
			$a_Cols[$i] = trim($a_Cols[$i]);
		}
		return $a_Cols;
	}

	/// 列字符串<-列数组
	public static function cColStrFromAry($a_Cols)
	{
		return is_string($a_Cols) ? $a_Cols : implode(',', $a_Cols);
	}

	/// 读取列
	/// $a_Cols：String，要读的列，用逗号分隔
	/// $a_Where：String，条件，关键字可省
	/// $a_OrderBy：String，排序，关键字可省
	/// $a_Limit：String，范围，关键字可省
	/// $a_Agms：Array，实参，默认null
	/// 返回：PDOStatement
	public static function cReadCols($a_Pdo, $a_Tbl, $a_Cols, 
									$a_Where = '', $a_OrderBy = '', $a_Limit = '',
									$a_Agms = null)
	{
		if (\hpnWse\fBool($a_Where) && 
			('WHERE ' !== strtoupper(\hpnWse\stStrUtil::cSub($a_Where, 0, 6))))
		{
			$a_Where = 'WHERE ' . $a_Where;
		}
		if (\hpnWse\fBool($a_OrderBy) &&
			('ORDER BY ' !== strtoupper(\hpnWse\stStrUtil::cSub($a_Where, 0, 9))))
		{
			$a_OrderBy = 'ORDER BY ' . $a_OrderBy;
		}
		if (\hpnWse\fBool($a_Limit) &&
			('LIMIT ' !== strtoupper(\hpnWse\stStrUtil::cSub($a_Where, 0, 6))))
		{
			$a_Limit = 'LIMIT ' . $a_Limit;
		}

$l_Sql = <<<SQL
SELECT $a_Cols
FROM $a_Tbl
$a_Where
$a_OrderBy
$a_Limit
SQL;
		++self::$c_QryCnt;
		$l_PdoStmt = $a_Pdo->prepare($l_Sql);
		$l_PdoStmt->execute($a_Agms);
		return $l_PdoStmt;
	}

	/// 读取列 - 比较
	/// $a_Cols：String，要读的列，用逗号分隔
	/// 返回：PDOStatement
	public static function cReadCols_Cmpr($a_Pdo, $a_Tbl, $a_Cols, $a_Col, $a_Opt, $a_Val)
	{
$l_Sql = <<<SQL
SELECT $a_Cols
FROM $a_Tbl
WHERE $a_Col $a_Opt ?
SQL;
		++self::$c_QryCnt;
		$l_PdoStmt = $a_Pdo->prepare($l_Sql);
		$l_PdoStmt->execute(array($a_Val));
		return $l_PdoStmt;
	}

	/// 读取行数 - 比较
	/// 返回：Number
	public static function cReadRowAmt_Cmpr($a_Pdo, $a_Tbl, $a_Col, $a_Opt, $a_Val)
	{
$l_Sql = <<<SQL
SELECT COUNT(*)
FROM $a_Tbl
WHERE $a_Col $a_Opt ?
SQL;

		++self::$c_QryCnt;
		$l_PdoStmt = $a_Pdo->prepare($l_Sql);
		$l_PdoStmt->execute(array($a_Val));
		$l_Row = $l_PdoStmt->fetch(\PDO::FETCH_NUM); // “["0"]”之类
		return intval($l_Row[0]);
	}

	/// 获取全部，不存在时返回空数组，若a_RtnNullWhenEmt为true则返回null
	public static function cFchAll($a_PdoStmt, $a_RtnNullWhenEmt = false)
	{
		$l_Rst = $a_PdoStmt->fetchAll(\PDO::FETCH_ASSOC);
		return ($a_RtnNullWhenEmt && (0 == count($l_Rst))) ? null : $l_Rst;
	}

	/// 获取一个，不存在时返回null
	public static function cFchOne($a_PdoStmt)
	{
		return \hpnWse\fV1OrV2($a_PdoStmt->fetch(\PDO::FETCH_ASSOC), null);
	}

	/// 获取数字，不存在时返回null
	public static function cFchNum($a_PdoStmt)
	{
		$l_Row = $a_PdoStmt->fetch(\PDO::FETCH_NUM); // “["0"]”之类
		return $l_Row ? intval($l_Row[0]) : null; // 空数组->false
	}

	/// 预备、执行，返回影响行数
	public static function cPrprExec($a_Pdo, $a_Sql, $a_Agms = null)
	{
		++self::$c_QryCnt;
		$l_PdoStmt = $a_Pdo->prepare($a_Sql);
		$l_PdoStmt->execute($a_Agms);
		return $l_PdoStmt->rowCount();
	}

	/// 预备、执行、获取多个
	public static function cPrprExecFchAll($a_Pdo, $a_Sql, $a_Agms = null, $a_RtnNullWhenEmt = false)
	{
		++self::$c_QryCnt;
		$l_PdoStmt = $a_Pdo->prepare($a_Sql);
		$l_PdoStmt->execute($a_Agms);
		return self::cFchAll($l_PdoStmt, $a_RtnNullWhenEmt);
	}

	/// 预备、执行、获取一个
	public static function cPrprExecFchOne($a_Pdo, $a_Sql, $a_Agms = null)
	{
		++self::$c_QryCnt;
		$l_PdoStmt = $a_Pdo->prepare($a_Sql);
		$l_PdoStmt->execute($a_Agms);
		return self::cFchOne($l_PdoStmt);
	}

	/// 预备、执行、获取数字
	public static function cPrprExecFchNum($a_Pdo, $a_Sql, $a_Agms = null)
	{
		++self::$c_QryCnt;
		$l_PdoStmt = $a_Pdo->prepare($a_Sql);
		$l_PdoStmt->execute($a_Agms);
		return self::cFchNum($l_PdoStmt);
	}

	/// 动作锁，用于限制同一个用户过于频繁地请求
	/// a_LockCol：String，用作锁的列名
	/// a_Lmt：Number，限制（秒）
	/// a_IdCol：String，ID列名
	/// a_Id：用户ID
	/// 返回：Boolean，若为true则获得锁（可以继续执行），若为false应当立即结束当前请求
	public static function cActnLock($a_Pdo, $a_Tab, $a_LockCol, $a_Lmt,
									$a_IdCol, $a_Id)
	{
$l_Sql = <<<SQL
UPDATE $a_Tab
SET $a_LockCol = NOW()
WHERE ($a_IdCol = :mId) AND (TIMESTAMPDIFF(SECOND, $a_LockCol, NOW()) >= :mLmt)
SQL;

		$l_Afcd = stSqlUtil::cPrprExec($a_Pdo, $l_Sql, 
			array(
				'mId' => $a_Id,
				'mLmt' => $a_Lmt
			));
		return (1 === $l_Afcd);
	}

	/// 融合多对多
	///【说明】主要用于将查询多对多关系得到的数组融合进对象数组中
	/// a_ObjAry：Object[]，对象数组，即融合的目的地
	/// a_ObjPk：String，对象主键，将用于和a_PartPk对比，相等就融合
	/// a_Key：String，融合键，即融合进每个对象的哪个属性里？一定是数组属性
	/// a_PartAry：Object[]，部分数组，即融合的来源
	/// a_PartPk：String，部分主键，将用于和a_ObjPk对比，相等就融合，null表示同a_ObjPk
	/// a_Which：String，把哪个属性装入a_Key标识的数组里？null表示除a_PartPk外的全部属性
	public static function cMgeMtm(&$a_ObjAry, $a_ObjPk, $a_Key, &$a_PartAry, $a_PartPk = null, $a_Which = null)
	{
		foreach ($a_PartAry as $l_OneKey => $l_One)
		{
			$l_Idx = \hpnWse\stAryUtil::cFind($a_ObjAry, 
				function ($a_Ary, $a_Idx, $a_Obj) use($a_ObjPk, $a_PartPk, $l_One)
				{ return $a_Obj[$a_ObjPk] == $l_One[$a_PartPk]; });
			if ($l_Idx >= 0) // 应该能找到！
			{
				if (null === $a_Which)
				{
					$l_Temp = $l_One; // 做个副本
					unset($l_Temp[$a_PartPk]);
					$a_ObjAry[$l_Idx][$a_Key][] = $l_Temp;
				}
				else
				{
					$a_ObjAry[$l_Idx][$a_Key][] = $l_One[$a_Which];
				}
			}
		}
	}

	/// 把部分列转成int型，因为PDO返回的总是String
	/// a_Cols：String，要转成int的列，用逗号分隔
	/// a_Rows：Object$Object[]，行或行数组
	public static function cIntCols($a_Cols, &$a_Rows)
	{
		$a_Cols = explode(',', $a_Cols);

		if (\hpnWse\fIsAry($a_Rows, true))
		{
			for ($i=0; $i<count($a_Rows); ++$i)
			{
				self::eIntCol($a_Cols, $a_Rows[$i]);
			}
		}
		else
		{
			self::eIntCol($a_Cols, $a_Rows);
		}
	}

	private static function eIntCol($a_Cols, &$a_Row)
	{
		for ($j=0; $j<count($a_Cols); ++$j)
		{
			$l_Col = trim($a_Cols[$j]);
			if (isset($a_Row[$l_Col]))
			{
				$a_Row[$l_Col] = intval($a_Row[$l_Col]);
			}
		}
	}

	/// 提取类型化列，例："C1, int C2, C3, int C4" -> "C1, C2, C3, C4" + "C2,C4"
	/// $a_Cols：String，列，带有类型信息
	/// $a_IntCols：String，将被填入int列，以逗号分隔，若没有int列则为空串
	public static function cExtrTypedCols(&$a_Cols, &$a_IntCols)
	{
		$a_IntCols = '';
		if (\hpnWse\stStrUtil::cFind($a_Cols, 'int ') < 0)
		{
			return;
		}

		$a_IntCols = array();
		$a_Cols = explode(',', $a_Cols);
		for ($j=0; $j<count($a_Cols); ++$j)
		{
			$a_Cols[$j] = trim($a_Cols[$j]);
			if ('int ' == \hpnWse\stStrUtil::cSub($a_Cols[$j], 0, 4))
			{
				$a_Cols[$j] = trim(\hpnWse\stStrUtil::cSub($a_Cols[$j], 4));
				$a_IntCols[] = $a_Cols[$j];
			}
		}
		$a_Cols = implode(',', $a_Cols);
		$a_IntCols = implode(',', $a_IntCols);
	}

	/// 构建占位符 - 赋值列表，用于INSERT和UPDATE语句
	/// $a_Cols：String，列，以逗号分隔
	/// $a_ForInsert：Boolean，用于INSERT？false表示UPDATE
	public static function cBldPlchds_AsnList($a_Cols, $a_ForInsert)
	{
		$a_Cols = explode(',', $a_Cols);
		$l_Plchds = array();
		for ($i=0; $i<count($a_Cols); ++$i)
		{
			$a_Cols[$i] = trim($a_Cols[$i]);
			if ($a_ForInsert)
			{
				$l_Plchds[] = ':' . $a_Cols[$i];
			}
			else
			{
				$l_Plchds[] = $a_Cols[$i] . '=:' . $a_Cols[$i];
			}
		}
		$l_Plchds = implode(',', $l_Plchds);
		return $l_Plchds;
	}

	/// 构建占位符 - 主键列表，用于WHERE子句
	/// $a_Pks：String，主键列，以逗号分隔
	/// $a_UseQmark：Boolean，使用问号？
	public static function cBldPlchds_PkList($a_Pks, $a_UseQmark = false)
	{
		$a_Pks = explode(',', $a_Pks);
		$l_Plchds = array();
		for ($i=0; $i<count($a_Pks); ++$i)
		{
			$a_Pks[$i] = trim($a_Pks[$i]);
			$l_Plchds[] = $a_UseQmark
				? ($a_Pks[$i] . '=?')
				: ($a_Pks[$i] . '=:' . $a_Pks[$i]);
		}
		$l_Plchds = '(' . implode(' AND ', $l_Plchds) . ')';
		return $l_Plchds;
	}

	/// 构建语句 - IN
	/// a_Val：要比较的值
	/// a_Dmn：Array，定义域，若为空则返回空串
	public static function cBldStmt_In($a_Val, $a_Dmn)
	{
		if (0 == count($a_Dmn))
		{ return ''; }

		$l_Rst = $a_Val;
		$l_Rst .= ' IN (';
		$l_Rst .= implode(',', $a_Dmn);
		$l_Rst .= ')';
		return $l_Rst;
	}

	/// 构建语句 - ORDER BY
	/// a_Map：Array，映射，把排序值映射成列，若不存在映射列则忽略
	/// a_SortBy：String，根据什么排序？多个值以逗号分隔
	/// a_SortDir：String，排序方向？“+”表示升序，“-”表示降序，必须与a_SortBy匹配
	/// 返回：String，若a_SortBy为空则返回空串，否则返回"ORDER BY ..."
	public static function cBldStmt_OrderBy($a_Map, $a_SortBy, $a_SortDir)
	{
		if (! $a_SortBy)
		{ return ''; }

		$l_Rst = '';
		$l_Bys = explode(',', $a_SortBy);
		$l_Dirs = explode(',', $a_SortDir);
		for ($i=0; $i<count($l_Bys); ++$i)
		{
			$l_By = $l_Bys[$i];
			if (! isset($a_Map[$l_By]))
			{ continue; }

			if ($l_Rst)
			{ $l_Rst .= ','; }
			else
			{ $l_Rst = 'ORDER BY '; }

			$l_Rst .= $a_Map[$l_By];
			if ('-' == $l_Dirs[$i])
			{
				$l_Rst .= ' ';
				$l_Rst .= 'DESC';
			}
		}
		return $l_Rst;
	}

	/// 更新列 - 比较
	/// $a_Cvp：Object，列值对儿
	public static function cUpdCols_Cmpr($a_Pdo, $a_Tbl, $a_Cvp, $a_Col, $a_Opt, $a_Val)
	{
		$l_Set = '';
		$l_Vals = array();
		foreach ($a_Cvp as $l_C => $l_V)
		{
			if ($l_Set) { $l_Set .= ', '; }
			$l_Set .= $l_C;
			$l_Set .= ' = ?';
			$l_Vals[] = $l_V;
		}
		$l_Vals[] = $a_Val;

$l_Sql = <<<SQL
UPDATE $a_Tbl
SET $l_Set
WHERE $a_Col $a_Opt ?
SQL;

		++self::$c_QryCnt;
		$l_PdoStmt = $a_Pdo->prepare($l_Sql);
		$l_PdoStmt->execute($l_Vals);
	}
}


} // namespace hpnWse

// PDO::lastInsertId

/*
// If you need to set an ISOLATION level or LOCK MODE it needs to be done BEFORE you make the BeginTransaction() call...
 //
 //  **note** you should always check result codes on operations and do error handling.  This sample code
 //  assumes all the calls work so that the order of operations is accurate and easy to see
 //
 //  THIS IS using the PECL PDO::INFORMIX module, running on fedora core 6, php 5.2.4
 //
 //    This is the correct way to address an informix -243 error (could not position within table) when there
 //    is no ISAM error indicating a table corruption.  A -243 can happen (if the table/indexes, etc., are ok) 
 //    if a row is locked.  The code below sets the LOCK MODE to wait 2 minutes (120 seconds) before
 //    giving up.  In this example you get READ COMMITTED rows, if you don't need read committed
 //    but just need to get whatever data is there (ignoring locked rows, etc.) instead of
 //    "SET LOCK MODE TO WAIT 120" you could "SET ISOLATION TO DIRTY READ".
 //
 //    In informix you *must* manage how you do reads because it is very easy to trigger a
 //    lock table overflow (which downs the instance) if you have lots of rows, are using joins
 //    and have many updates happening.  
 //

 // e.g.,

 $sql= "SELECT FIRST 50 * FROM mytable WHERE mystuff=1 ORDER BY myid";                    // define SQL query 

 try                                                                                // create an exception handler
     {
     $dbh = new PDO("informix:host=......");
          
     if ($dbh)    // did we connect?
         {
         $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         $dbh->query("SET LOCK MODE TO WAIT 120")
         
         # ----------------
         # open transaction cursor
         # ----------------
         if    ( $dbh->beginTransaction() )                                         # explicitly open cursor
             {
             try   //  open exception handler 
                 {
                 $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

                 $stmt->execute();
                 
                 while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT))
                     {
                     $data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\t" . $row[3] . "\t" . $row[4] . "\t" . $row[5] . "\t" . $row[6] . "\t" . $row[7] . "\n" . $row[8] ;
                     //print $data;
                     print_r($row);
                     };
                 
                 $stmt = null;
                 }
             catch (PDOException $e)
                 {
                 print "Query Failed!\n\n";
                 
                 print "DBA FAIL:" . $e->getMessage();
                 };
             
             $dbh->rollback();                                                       # abort any changes (ie. $dbh->commit()
             $dbh = null;                                                            # close connection
             }
         else
             {
             # we should never get here, it should go to the exception handler
             print "Unable to establish connection...\n\n";
             };
         };
     }
 catch (Exception $e)
     {
     $dbh->rollback();
     echo "Failed: " . $e->getMessage();
     }; 

*/

//////////////////////////////////// OVER ////////////////////////////////////